<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import từ file excel</title>
<style type="text/css">
</style><link href="../css/style.css" rel="stylesheet" type="text/css" />
<link href="../SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<script src="../SpryAssets/SpryMenuBar.js" type="text/javascript"></script>
</head>

<body>

<form action="" method="post" enctype="multipart/form-data" name="form1">
  <p>&nbsp;</p>
  <p>
    <input type="file" name="file" id="file">
    <input type="submit" name="upload" id="upload" value="Submit">
  </p>
  <p>&nbsp;</p>
</form>
<?php 
include_once($_SERVER['DOCUMENT_ROOT'].'/QL MBA/Models/MBA.php');
include_once($_SERVER['DOCUMENT_ROOT'].'/QL MBA/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');
include_once($_SERVER['DOCUMENT_ROOT'].'/QL MBA/PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php');

if( isset($_POST['upload']) ){
	if( $_FILES["file"]["name"] != "" ){
		


$objPHPExcel = PHPExcel_IOFactory::load($_FILES["file"]["tmp_name"]);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
foreach ( $objWorksheet->getRowIterator() as $row) {
     $worksheetTitle = $objWorksheet->getTitle();
     $highestRow = $objWorksheet->getHighestRow(); // e.g. 10
     $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
     $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
}

//
$nrColumns = $highestColumnIndex;


//
echo "File ".$worksheetTitle." has ";
echo $nrColumns . ' columns';
echo ' y ' . $highestRow . ' rows.';

// quang vao csdl
for ($row = 4; $row <= $highestRow; ++ $row) {
	$val=array();
for ($col = 0; $col < $nrColumns;  $col++) {
   $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
   $val[] = $cell->getValue();
 }
 
  // $sql="insert into tablename(column1, column2, column3, column4, column5, column6)
  // values('".$val[1] . "','" . $val[2] . "','" . $val[3]. "','" . $val[4]. "','" . $val[5]. "','" . $val[6]. "')";
//Run your mysql_query
	$MBA = new MBA();
	
    // thong tin chinh - bang may_bien_ap
			 
		 $SO_N0 = $val[0];
		 $MSTS = $val[1];
		 $CONG_SUAT = $val[3];
		 $LOAI_DIEN_AP = $val[2];
		 // do MA_DIEN_AP tu LOAI_DIEN_AP trong CSDL
		 $db = new database();
		 $db->setQuery("select * from loai_dien_ap");
		 $result = $db->fetchAll();
		 while($rows  = mysql_fetch_array($result)){
                if( $LOAI_DIEN_AP==$rows['CAP_DIEN_AP']){ $MA_DIEN_AP=$rows['MA_DIEN_AP']; }
            }
		// tu file excel la NHASX ~> MA_NHA_SX
		 $TEN_NSX = $val[10];
		 $db = new database();
		 $db->setQuery("select * from nha_sx");
		 $result = $db->fetchAll();
		 while($rows  = mysql_fetch_array($result)){
                if( $TEN_NSX==$rows['TEN_NSX']){ $MA_NSX=$rows['MA_NSX']; }
            }

		 
		 // loai may' 
		 if($val[23]== "x"){ $MA_LOAI= 1;}
		 if($val[24]== "x"){ $MA_LOAI= 2;}
		 
		 $CHIEU_DAI = $val[4];
		 $CHIEU_RONG = $val[5];
		 $CHIEU_CAO = $val[6];
		 $DAU_MAY = $val[7];
		 $RUOT_MAY = $val[8];
		 $TONG = $val[9];
		 $NAM_SX = $val[12];
		 $NAM_NHAP_VE = $val[15];
		 
		 $THONG_SO_DO=$val[28];
		 $NHAN_XET=$val[29];
		 $GHI_CHU=$val[30];
		 
		 // Thong tin cua bang chi_tiet_dai_tu
		 $NGAY_DAI_TU = $val[32];
		  // bang chi_tiet_ngung_van_hanh
		 $NGAY_NGUNG_VH = $val[31];
		 // bang chi_tiet_tt_may
		 $TEN_TINH_TRANG_MAY=$val[25];
		 $TINH_TRANG_NGAY=$val[26];
		 $DG_TINH_TRANG=$val[27];
		 
		 // bang loai_dau
		 $MA_LOAI_DAU = "";
		 if($val[16] == "x"){ $MA_LOAI_DAU = 1;}
		 if($val[17] == "x"){ $MA_LOAI_DAU = 2;}
		 if($val[18] == "x"){ $MA_LOAI_DAU = 3;}
		 if($val[19] == "x"){ $MA_LOAI_DAU = 4;}
		 if($val[20] == "x"){ $MA_LOAI_DAU = 5;}
		 if($val[21] == "x"){ $MA_LOAI_DAU = 6;}
		 $TEN_LOAI_DAU = $val[22];
		 if ($MA_LOAI_DAU == ""){			 
			 // kiem tra TEN_LOAI_DAU moi co trong CSDL chua -> neu co thi lay MA_LOAI_DAU tuong ung
			 $db = new database();
			 $db->setQuery("SELECT * FROM loai_dau");
		 	 $result = $db->fetchAll();
		 while($rows = mysql_fetch_array($result)){
                if ($TEN_LOAI_DAU == $rows['TEN_LOAI_DAU']){ $MA_LOAI_DAU =$rows['MA_LOAI_DAU'] ;}
            }
			
		if ($MA_LOAI_DAU == ""){	
			 // tang MA_LOAI_DAU trong CSDL len 1
			  $db = new database();
		 $db->setQuery("SELECT MAX(MA_LOAI_DAU) FROM loai_dau");
		 $result = $db->fetchAll();
		 while($rows = mysql_fetch_array($result)){
               $MA_LOAI_DAU = $rows['MAX(MA_LOAI_DAU)'];
            }
			
			 $MA_LOAI_DAU=$MA_LOAI_DAU+1;
			 

		  //them MA_LOAI_DAU + TEN_LOAI_DAU moi VAO CSDL
		  // them vao bang loai_dau ----------------------------------------------------------------
		    $db = new database();
			$db->setQuery("insert into loai_dau(MA_LOAI_DAU,TEN_LOAI_DAU) 
            values ('".$MA_LOAI_DAU."','".$TEN_LOAI_DAU."')");
			$db->executeQuery();
		 }
		 }
		 
		 // bang mba_tram
		 $TEN_TRAM = $val[13];
		 // lay MA_TRAM TU TEN_TRAM CO DUOC TU FILE EXCEL
		 $db = new database();
		 $db->setQuery("select * from tram");
		 $result = $db->fetchAll();
		 while($rows  = mysql_fetch_array($result)){
                if( $TEN_TRAM==$rows['TEN_TRAM']){ $MA_TRAM=$rows['MA_TRAM']; }
            }
		 $NGAY_VAN_HANH = $val[14];
		 // BANG chi_tiet_so_huu
		 //lay MA_DON_VI TU TEN_DON_VI CO DUOC TU FILE EXCEL
		  $TEN_DON_VI = $val[33];
		  
		  $db = new database();
		 $db->setQuery("select * from don_vi");
		 $result = $db->fetchAll();
		 while($rows  = mysql_fetch_array($result)){
                if( $TEN_DON_VI==$rows['TEN_DON_VI']){ $MA_DON_VI=$rows['MA_DON_VI']; }
            }
		  
		  $NGAY_CHUYEN_DEN = $val[34];
		

		

		
	
	// gan cac thong tin tu 1 dong cua file excel vao 1 doi tuong -> them vao csdl
    $MBA->setSO_N0($SO_N0);
    $MBA->setMSTS($MSTS);
	$MBA->setCONG_SUAT($CONG_SUAT);
	$MBA->setMA_NSX($MA_NSX);
	$MBA->setMA_LOAI($MA_LOAI);
	$MBA->setCHIEU_DAI($CHIEU_DAI);
	$MBA->setCHIEU_RONG($CHIEU_RONG);
	$MBA->setCHIEU_CAO($CHIEU_CAO);
	$MBA->setDAU_MAY($DAU_MAY);
	$MBA->setRUOT_MAY($RUOT_MAY);
	$MBA->setTONG($TONG);
	$MBA->setNAM_SX($NAM_SX);
	$MBA->setNAM_NHAP_VE($NAM_NHAP_VE);
	$MBA->setTHONG_SO_DO($THONG_SO_DO);
	$MBA->setNHAN_XET($NHAN_XET);
	$MBA->setGHI_CHU($GHI_CHU);
	$MBA->setMA_DIEN_AP($MA_DIEN_AP);
	$MBA->setNGAY_DAI_TU($NGAY_DAI_TU);
	$MBA->setNGAY_NGUNG_VH($NGAY_NGUNG_VH);
	$MBA->setTEN_TINH_TRANG_MAY($TEN_TINH_TRANG_MAY);
	$MBA->setTINH_TRANG_NGAY($TINH_TRANG_NGAY);
	$MBA->setDG_TINH_TRANG($DG_TINH_TRANG);
	$MBA->setMA_LOAI_DAU($MA_LOAI_DAU);
	$MBA->setTEN_LOAI_DAU($TEN_LOAI_DAU);
	$MBA->setTEN_TRAM($TEN_TRAM);
	$MBA->setMA_TRAM($MA_TRAM);
	$MBA->setNGAY_VAN_HANH($NGAY_VAN_HANH);
	$MBA->setTEN_DON_VI($TEN_DON_VI);
	$MBA->setMA_DON_VI($MA_DON_VI);
	$MBA->setNGAY_CHUYEN_DEN($NGAY_CHUYEN_DEN);
	
	
	
	  $ketquaip="ketqua";

    if ($MBA->isExits("may_bien_ap","SO_N0='".$MBA->getSO_N0()."'") <= 0  ){
            if ($MBA->themMBA() > 0){
        			$ketquaip=" OK !";
            }
            else{
                	$ketquaip="Khong thanh cong";
            } 
        }
        else{
            //MBA da ton tai
            	$ketquaip="Bi trung so N0";
        }
		
		
		// hien thi ra bang thong bao cac may import
echo 'Data: <table cellpadding="3" cellspacing="1" border="0" align="center" width="3500"><tr>';
// header cua bang hien thi
echo "<tr>";
echo "<th rowspan='2' class='TableHeader'>Kết quả Import</th>";
echo "<th rowspan='2' class='TableHeader'>Số N0</th>";
  echo "    <th rowspan='2' class='TableHeader' >MSTS</th>";
 echo "     <th rowspan='2' class='TableHeader' width='100'>Loại điện áp (U)</th>";
 echo "     <th rowspan='2' class='TableHeader' width='100'>Công suất (S)</th>";
echo "      <th rowspan='2' class='TableHeader' width='100'>Chiều dài (m)</th>";
   echo "   <th rowspan='2' class='TableHeader' width='100'>Chiều rộng (m)</th>";
   echo "   <th rowspan='2' class='TableHeader' width='100'>Chiều cao (m)</th>";
    echo "  <th rowspan='2' class='TableHeader' width='100'>Dầu máy (kg)</th>";
   echo "   <th rowspan='2' class='TableHeader' width='100'>Ruột máy (kg)</th>";
    echo "  <th rowspan='2' class='TableHeader' width='50'>Tổng (kg)</th>";
    echo "  <th rowspan='2' class='TableHeader'>Nhà SX</th>";
    echo "  <th rowspan='2' class='TableHeader'>Nước sản xuất</th>";
   echo "   <th rowspan='2' class='TableHeader' width='50'>Năm sản xuất</th>";
   echo "    <th rowspan='2' class='TableHeader'>Tên trạm</th>";
  echo "    <th rowspan='2' class='TableHeader'>Ngày vận hành</th>";
     echo " <th rowspan='2' class='TableHeader' width='50'>Năm nhập về</th>";
  echo "    <th colspan='7' class='TableHeader'>Loại dầu</th>";
   echo "   <th colspan='2' class='TableHeader'>Loại máy</th>";
    echo "  <th rowspan='2' class='TableHeader'>Tình trạng máy</th>";
    echo "  <th rowspan='2' class='TableHeader'>Có tình trạng</th>";
     echo " <th rowspan='2' class='TableHeader'>Diễn giải tình trạng</th>";
   echo "   <th rowspan='2' class='TableHeader'>Thông số đo</th>";
   echo "   <th rowspan='2' class='TableHeader'>Nhận xét</th>";
    echo "  <th rowspan='2' class='TableHeader' width='200'>Ghi chú</th>";
      echo "<th rowspan='2' class='TableHeader'>Ngày ngưng</th>";
    echo "  <th rowspan='2' class='TableHeader'>Ngày đại tu</th>";
	echo "  <th rowspan='2' class='TableHeader'>Tên đơn vị</th>";
	echo "  <th rowspan='2' class='TableHeader'>Ngày chuyển đến</th>";
   echo " </tr>";
   
    echo " <tr>";
     echo "  <td class='TableHeader'>Castrol</td>";
     echo "  <td class='TableHeader'>Nynas</td>";
      echo " <td class='TableHeader'>DIALA_A</td>";
      echo " <td class='TableHeader'>DIALA-AX</td>";
      echo " <td class='TableHeader'>APBLUE</td>";
      echo " <td class='TableHeader'>SUPER-T</td>";
      echo " <td class='TableHeader'>Loại dầu khác</td>";
       echo "<td class='TableHeader'>1 pha</td>";
      echo " <td class='TableHeader'>3 pha</td>";
   echo "  </tr>";



// hien thi thong tin bang excel
for ($row = 4; $row <= $highestRow; ++ $row) {

	// bat dau lay du lieu tu file excel hien len
   echo '<tr>';
  // truoc moi dong du lieu them vao ket qua import
  
  if($row%2 ==0 ){
           echo '<td align="center" class="TableField2" > ' . $ketquaip . '</td>';
	   } else { 
	   echo '<td align="center" class="TableField" > ' . $ketquaip . '</td>';}
	   // in du lieu tu file excel
   for ($col = 0; $col < $nrColumns; ++ $col) {
	   $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
       $val = $cell->getValue();

		
	   if($row%2 ==0 ){
           echo '<td align="center" class="TableField2" > ' . $val . '</td>';
	   } else { 
	   echo '<td align="center" class="TableField" > ' . $val . '</td>';}
	
   }
echo '</tr>';

}
echo '</table>';

		
		
}

	} else { echo "Chon file excel de upload!";}

}
?>


</body>
</html>





